#!/usr/local/bin/php
<?php
/**
 * 1.mysql语句转为markdown文档
 * 2.直接连接数据库,把所有数据表转化为markdown文件,或指定数据表转化为markdown文件
 */

if ($argc < 3){
	usage();
}

if ($argv[1] == 'file') {
	$sqlFile = $argv[2];
	if (!file_exists($sqlFile)) {
		echo "文件不存在!".PHP_EOL;
		exit;
	}
	echo DB2Markdown::parseFile($sqlFile),PHP_EOL;

} else if ($argv[1] == 'db') {
	if ($argc != 6 && $argc != 7) {
		usage();
	}
	$host = $argv[2];
	$dbname = $argv[3];
	$user = $argv[4];
	$password = $argv[5];
	$table = isset($argv[6]) ? $argv[6] : '';
	$dsn = 'mysql:host='.$host.';dbname='.$dbname.';port=3306;charset=utf8';
	echo DB2Markdown::parseDB($dsn, $user, $password, $table); 

} else if ($argv[1] == 'qdb') {
	if($argc != 3 && $argc != 4) {
		usage();
	}
	$dbname = $argv[2];
	$table = $argv[3];
	$dsn = 'mysql:host=localhost;dbname='.$dbname.';port=3306;charset=utf8';
	echo DB2Markdown::parseDB($dsn, 'root', '1234', $table); 

}
else {
	usage();
}

function usage()
{
	echo "使用说明:".PHP_EOL;
	echo "1.解析sql文件命令:".PHP_EOL;
	echo basename(__FILE__).' file sqlfile'.PHP_EOL.PHP_EOL;
	echo "2.解析数据库:".PHP_EOL; 
	echo basename(__FILE__).' db dbhost dbname dbuser dbpasswd [table]'.PHP_EOL.PHP_EOL;
	echo "3.我的默认数据库方式:".PHP_EOL;
	echo basename(__FILE__).' qdb dbname [table]'.PHP_EOL.PHP_EOL;
	exit;
}

class DB2Markdown
{
	const LS = PHP_EOL;
	const FS = "\t";

	//数据库对象
	private static $db;

	//创建表头
	private static function createTh($tableName, $nameCn = '')
	{
		$sArr = [];
	   	$sArr[] = '###'.self::safeName($tableName).($nameCn ? '('.$nameCn.')' : '').self::LS;
		$sArr[] = '字段'.str_repeat(self::FS, 2)."| ".'类型'.str_repeat(self::FS,2)."| ".'字段说明'.self::FS;
		$sArr[] = str_repeat(str_repeat('-', 8).self::FS.'| ', 2).str_repeat('-',8);
		return join(self::LS, $sArr).self::LS;
	}

	//拼接成markdown中的table行
	private static function createField($name, $type, $comment = '')
	{
		return self::safeName($name).self::FS.'| '.$type.self::FS.'| '.$comment;
	}

	//避免多个下划线单词出现错误
	private static function safeName($name)
	{
		if (($n = substr_count($name, '_')) > 1) {
			$name = preg_replace('/_/', '\\_', $name, $n - 1);
		}
		return $name;
	}

	private static function getTableName($str) 
	{
		preg_match('/CREATE TABLE `(.+?)`/', $str, $match);
		return $match[1];
	}

	private static function getTableComment($str)
	{
		if (preg_match('/COMMENT=\'(.+?)\';/', $str, $match)) {
			return $match[1];
		}
	}
	
	private static function parseField($str)
	{
		$str = trim($str);
		if($str && preg_match('/^`(.+?)`\s(.+?)\s.*?(COMMENT\s\'(.*?)\')?,$/',$str, $matchs)) {
			return self::createField($matchs[1], self::getFieldType($matchs[2]), $matchs[4]);
		}
	}

	//获得字段类型
	private static function getFieldType($type)
   	{
		if (strpos($type, '(')) {
			$t = strstr($type, '(', true);
		} else {
			$t = $type;
		}
		if (in_array($t, ['int', 'tinyint', 'smallint', 'bigint', 'mediumint', 'timestamp'])) {
			return 'int';
		}
		if (in_array($t, ['float', 'double', 'decimal'])) {
			return 'float';
		}
		if (in_array($t, ['char', 'varchar', 'tinytext', 'text', 'mediumtext', 'longtext', 'date', 'datetime'])) {
			return 'string';
		}
		if (stripos($t, 'blob') !== false) {
			return 'blob';
		}
	}	

	/**
	 * sql文件生产markdown数据字典
	 * @param $fname string sql文件路径
	 * return string
	 */
	public static function parseFile($fname)
	{
		if (!file_exists($fname)) {
			trigger_error('文件不存在');
		}
		$sql = file_get_contents($fname);
		if (preg_match_all('/(create table .+?\) engine=.+?;)/sim', $sql, $matchs)) {
			$rtn = '';
			foreach ($matchs[1] as $table) {
				$rtn .= self::parseCreateSql($table);
			}
			return $rtn;
		}
	}

	//解析建表语句
	private static function parseCreateSql($createTableSql) 
	{
		if (!$createTableSql) {
			return;
		}
		$lines = explode("\n", $createTableSql);
		$tableName = self::getTableName(array_shift($lines));	
		$tableComment = self::getTableComment(array_pop($lines));
		$tb = self::createTh($tableName, $tableComment);
		foreach ($lines as $line) {
			$f = self::parseField($line);
			if ($f) {
				$tb .= $f.self::LS;
			}
		}
		return $tb.self::LS;
	}

	private static function connectDB($dsn, $uname, $passwd) 
	{
		try {
			self::$db = new PDO($dsn, $uname,$passwd);
			self::$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		} catch(PDOException $e) {
			echo "连接数据库失败\n";
			exit;
		}
	}

	//获取数据库中所有数据表
	private static function getAllTables() 
	{
		if (!self::$db) {
			echo "请先正确连接数据库\n";
			exit;
		}
		$stmt = self::$db->query("show tables");
		return $stmt->fetchAll(PDO::FETCH_COLUMN);	
	}

	//解析数据表
	private static function parseTable($table) 
	{
		if (!$table) {
			return;
		}
		$stmt = self::$db->query("show create table ".$table);
		$sql = $stmt->fetch(PDO::FETCH_NUM);
		return self::parseCreateSql($sql[1]);
	}

	public static function parseDB($dsn, $uname = '', $passwd = '', $table = '')
	{
		self::connectDB($dsn, $uname, $passwd);
		if ($table) {
			return self::parseTable($table);	
		} else {
			$tableArr = self::getAllTables();
			$rtn = '';
			foreach($tableArr as $table) {
				$rtn .= self::parseTable($table);
			}
			return $rtn;
		}
	}
}
